Sorting

Sorting allows the user to choose the order in which data and items are presented in a visualization. Quick sorting offers a context-driven selection of what to sort and a set of basic sort algorithms through a point-and-click menu option. The Sort wizard dialog (covered in this topic) offers an ability to create one or more sorts with greater control over the sorting logic and mechanics. It is also the venue to edit and refine quick sorts created through query interactions.

Sort Logic

Sorting logic is broken into three main constructs:

• List: The first is the listing of elements to sort
• Logic: The second to is to choose the sort type
• Values: The last, which is optional, is to describe the values that will drive the sorting operation.

Accessing the Sorting Wizard

Open the Sort Wizard by selecting Sort from the Home or Query ribbon or right clicking on in the white space of a visualization background. (Do not confuse this with the 'quick sort' menu option).

Sort Targets

To add a sort target (the hierarchy(s) that should be sorted), click the blue plus sign in the Sort Targets header. From the Sort panel, select the hierarchy(s) to be sorted. You can elect to sort a single hierarchy of multi-hierarchy combination. You can even add multiple target definitions to add different sorting logic for each target.

When adding a new sort, the member hierarchies within the query will appear as the basis of the sort. You can base your sorting preferences on any combination of the member hierarchies currently in the query, and any data point in your model even if those data points are not visible in the query.

Sort Settings

Sort Types

The sort type determines the overall sorting approach to be used. The type options change depending on the hierarchies chosen and the data source type.

Note: Hierarchy elements are always presented in their 'natural sort order' by default. The natural order is usually defined in the underlying data model and is typically alphabetical. Specifying a sort, breaks that natural ordering.

• Values: sorts the hierarchy elements based on numerical values. The numerical values or data points can be specified independently. See below.
• Labels: sorts the hierarchy elements using their labels or captions. This is effectively an alphabetical sort unrelated to values.
• Custom: sorts the hierarchy elements in the order they were selected from the elements tree - as opposed to their natural order or logical order. Since this is effectively a "no sort" option, all other sorting options are ignored. See below for more.
• Inverted: this sorts the elements of a multi-level hierarchy such that parent elements appear after child elements. This capability is only available for MS OLAP and SAP BW hierarchies.

Sort Order

Select the direction of the sort.

• Ascending: sort elements in ascending order from the element with the smallest to the largest data point value, or sort labels alphabetically from A to Z.
• Descending: sort elements from highest to lowest, or labels in reverse-alphabetical order Z to A.

Break Hierarchies

When sorting across 2 or more hierarchies, users to decide if the elements should be evaluated in a nested sequence and sorted in hierarchical context, or whether the sort should be agnostic of such structures and sort elements independently - effectively breaking context.

For example, looking at this simple grid, with the data shown in its 'natural' order, we can see the elements are listed alphabetically: Accessories then Clothing; while the colors are shown from black to Silver.

If I build a sort using both product category and color, and choose to sort with the default measure (which the measure currently used in the query itself - margin), the grid will appear as such with the 'break' switch off. This says sort category ascending, then within that, sort color from high to low margin.

Enabling the 'break' option, the grid changes to the following, with each item appearing in its numerical position. Accessories/NA with 658 is the largest item and therefore appears at the bottom of the list. In the scenario, its not unusual to see elements appear multiple times in the grid (like Accessories).

Select Hierarchy & Element

Data Points and Values for Sorting

When sorting elements based a 'values', we are effectively choosing to sort the elements according to some data point in the data model. Using grids above, we are sorting the category-color combinations based on the 'margin' data point or value for each item.

When you use Quick Sorts or the more complete Sorting Wizard, the data point is heuristically determined from the query itself or the context of the query (filters etc). However, you are not limited to using these items. You are free to completely define a different data point to use for the sort order. The data point can be a simple measure selection or be a much deeper selection of elements from anywhere in the data model - regardless of whether it can be seen in the query itself or not.

For example, the above grids simply choose margin. The user could decide to sort the items using 'Sales figures for Male customers in 2018' for each category-color combination instead. You can see below that the resulting grid shows the same margin values for category-color combinations, but the order is different to the above.

The flexibility to define an independent data point to drive your sorts is a very powerful option. To pick the items for the data point, we use the hierarchy and element pickers, described below.

Making Data Point Selections for Sorts

By default, sorting is configured according to the measure(s) in the query and anything else driving 'context'. Context means things like slicers and other query functions. To change the metric or data point, optionally select elements from each hierarchy, starting with any other measure.

Once you have selected a hierarchy, then select the specific element or measure from the elements panel.

Note:

• You can only pick one element from a given hierarchy.
  • If you need to pick more than one item, you will need to first build a composite or custom member, and then select that from the custom measure / member listing in the elements tree panel.
  • You can parameterize the selection using parameterized calculated members or measures.

Custom Sorting

To facilitate the custom ordering of hierarchy elements (or 'members'), users can elect to use the "Custom Sort" option. This switch tells the query engine to sort elements in the order they were picked when using the element trees, rather than their natural order, another sort mechanism or a specific ordering logic as defined and set inside a Custom List definition. Users can adjust this further using the "Set Element Order" dialog from the context menu.

Report based Sorting Wizard vs Sorting in a Custom List

Users are sometimes confused by these 2 capabilities and how they interoperate.

The Sort Wizard (and the 'Quick Sort') allow the user to sort the results of a query and save those settings into the report definition. Sorting steps added in Custom Lists designs are saved into the list definition, independent of its use in a report. Custom List sorting is therefore very useful for building highly specific, curated element lists that will be reused over and over, while sorts from a report are more ad-hoc in nature and usually deployed based on the needs of the specific report only.

Obviously, there is a logical overlap in both approaches - and the ability to choose from both strategies offers tremendous scope for designing the smartest analytic reporting to match your business needs.

Both mechanisms can be employed into the same report, each with multiple sorting instances if needed (multiple report sorts, multiple list sorts etc). Generally, if the sorts clash, the reporting sort will override the sorts of the lists.

Examples